<?php
class XSQLCriteriaTranslator implements XCriteriaTranslator{
	protected $driver = null;
	
	public function setDriver($driver){
		$this->driver = $driver;
	}
	
	public function translate(XTable $table, XCriteria $criteria){
		$condition = $this->translateCriteria($table, $criteria);
		if(strlen($condition) > 0){
			$condition = 'WHERE '.$condition;
		}
		$projections = $criteria->projections();
		if(count($projections) == 0){
			return "SELECT * FROM `{$table->name()}` {$condition}";
		}
		if(isset($projections[ORDER])){
			$condition.= " ORDER BY ";
			$orders = array();
			foreach($projections[ORDER] as $projection){
				$asc = $projection->alias();
				$orders[] = $projection->value().' '.($asc ? 'ASC' : 'DESC');
			}			
			$condition.= implode(' , ', $orders);
		}
		if(isset($projections[SIZE])){
			$condition.= " LIMIT ".$projections[SIZE]->value();
		}
		if(isset($projections[OFFSET])){
			$condition.= " OFFSET ".$projections[OFFSET]->value();
		}
		if(isset($projections[RESULT])){
			$value = addslashes($projections[RESULT]->value());
			return "SELECT {$value} FROM `{$table->name()}` {$condition}";
		}
		if(isset($projections[COUNT])){
			$value = $projections[COUNT]->value();
			$value = is_null($value) ? '*' : '\''.$value.'\'';
			return "SELECT COUNT({$value}) FROM `{$table->name()}` {$condition}";
		}
		if(isset($projections[SUM])){
			return "SELECT SUM({$projections[SUM]->value()}) FROM `{$table->name()}` {$condition}";
		}
		if(isset($projections[AVG])){
			return "SELECT AVG({$projections[AVG]->value()}) FROM `{$table->name()}` {$condition}";
		}
		if(isset($projections[MAX])){
			return "SELECT MAX({$projections[MAX]->value()}) FROM `{$table->name()}` {$condition}";
		}
		if(isset($projections[MIN])){
			return "SELECT MIN({$projections[MIN]->value()}) FROM `{$table->name()}` {$condition}";
		}
		return "SELECT * FROM `{$table->name()}` {$condition}";
	}
	
	protected function translateCriteria(XTable $table, XCriteria $criteria){
		$queries = array();
		$criterias = array();
		foreach($criteria->criterias() as $key => $criterion){
			if(XClassLoader::loader($criterion)->hasAncestor('XCriteria')){
				$criterias[] = $criterion;
				continue;
			}
			switch($criterion->type()){
				case EQUAL:
					$queries[] = "`{$criterion->field()}` = '{$criterion->value()}'";
					break;
				case NOT_EQUAL:
					$queries[] = "`{$criterion->field()}` != '{$criterion->value()}'";
					break;
				case GREATER:
					$queries[] = "`{$criterion->field()}` > '{$criterion->value()}'";
					break;
				case NOT_GREATER:
					$queries[] = "`{$criterion->field()}` <= '{$criterion->value()}'";
					break;	
				case LESS:
					$queries[] = "`{$criterion->field()}` < '{$criterion->value()}'";
					break;
				case NOT_LESS:
					$queries[] = "`{$criterion->field()}` >= '{$criterion->value()}'";
					break;	
				case IN:
					$results = array();
					foreach($criterion->value() as $value){
						$results[] = "'$value'";
					}
					if(count($results) == 0){
						break;
					}
					$queries[] = "`{$criterion->field()}` IN ( ".implode(' , ', $results)." )";
					break;
				case NOT_IN:
					$results = array();
					foreach($criterion->value() as $value){
						$results[] = "'$value'";
					}
					if(count($results) == 0){
						break;
					}
					$queries[] = "`{$criterion->field()}` NOT IN ( ".implode(' , ', $results)." )";
					break;
				case LIKE:
					$value = $criterion->value();
					switch($criterion->alias()){
						case LIKE_ANYWHERE:
							$value = '%'.$value.'%';
							break;
						case LIKE_START:
							$value = $value.'%';
							break;
						case LIKE_END:
							$value = '%'.$value;
							break;
						case LIKE_EXACT:
							break;										
					}
					$queries[] = "`{$criterion->field()}` LIKE '{$value}'";
					break;
				case BETWEEN:
					$queries[] = "`{$criterion->field()}` BETWEEN '{$criterion->value()}' AND '{$criterion->alias()}'";
					break;	
				case IS_NULL:
					$queries[] = "`{$criterion->field()}` IS NULL";
					break;
				case NOT_NULL:
					$queries[] = "`{$criterion->field()}` IS NOT NULL";
					break;	
				case EXAMPLE:
					foreach($table->columns() as $key => $column){
						$value = $criterion->value()->get($key);
						if(!is_null($value)){
							$queries[] = "`{$key}` = '{$value}'";
						}						
					}					
					break;
				case REGEXP:
					$queries[] = "`{$criterion->field()}` REGEXP '{$criterion->value()}'";
					break;																								
			}
		}		
		$sql = "";
		if(count($queries) > 0){			
			$sql = '('.implode(') AND (', $queries).')';
		}
		foreach($criterias as $criterion){
			switch($criterion->type()){
				case Criteria_AND:
					$andSQL = $this->translateCriteria($table, $criterion);
					if(strlen($andSQL) > 0){
						$sql = strlen($sql) > 0 ? $sql.' AND '.$andSQL : $andSQL;
					}
					break;
				case Criteria_OR:
					$orSQL = $this->translateCriteria($table, $criterion);
					if(strlen($orSQL) > 0){
						$sql = strlen($sql) > 0 ? $sql.' OR '.$orSQL : $orSQL;
					}
					break;		
			}
		}
		return $sql;
	}
}
?>